package com.zcurd.common.tool;

import java.util.List;

import com.jfinal.plugin.activerecord.Db;
import com.jfinal.plugin.activerecord.Record;
import com.zcurd.common.util.StringUtil;

/**
 * 
 * @author xpg
 *
 */
public class DBTool{
	public static final DBTool me = new DBTool();
	
	
	/*public static List<Record> findByMultProperties(String table, String[] properties, Object[] values) {
		return findByMultProperties(table, properties, values, null, null);
	}
	
	public static List<Record> findByMultProperties(String table, String[] properties, Object[] values, Pager pager) {
		return findByMultProperties(table, properties, values, null, pager);
	}*/
	
	public static List<Record> findByMultProperties(String table, String[] properties, Object[] values, String orderBy, Pager pager) {
		String[] symbols = new String[properties.length];
		for (int i = 0; i < properties.length; i++) {
			symbols[i] = "=";
		}
		return findByMultProperties(null,table, properties, symbols, values, orderBy, pager);
	}
	
	public static List<Record> findByMultProperties(String table, String[] properties, String[] symbols, Object[] values) {
		return findByMultProperties(null,table, properties, symbols, values, null, null);
	}
	
	public static List<Record> findByMultProperties(String table, String[] properties, String[] symbols, Object[] values, Pager pager) {
		return findByMultProperties(null, table, properties, symbols, values, null, pager);
	}
	
	public static List<Record> findByMultProperties(String table, String[] properties, String[] symbols, Object[] values, String orderBy, Pager pager) {
		return findByMultProperties(null,table, properties, symbols, values, orderBy, pager);
	}
	
	public static List<Record> findByMultProperties(String[] fields, String table, String[] properties, Object[] values) {
		return findByMultProperties(fields, table, properties, null, values, null, null);
	}
	
	public static List<Record> findByMultProperties(String[] fields, String table, String[] properties, String[] symbols, Object[] values) {
		return findByMultProperties(fields, table, properties, symbols, values, null, null);
	}
	
	public static List<Record> findByMultProperties(String[] fields, String table, String[] properties, String[] symbols, Object[] values, Pager pager) {
		return findByMultProperties(fields, table, properties, symbols, values, null, pager);
	}
	
	/*public static List<Record> findByMultProperties(String[] fields, String table, String[] properties, String[] symbols, Object[] values, String orderBy, Pager pager) {
		return findByMultProperties(fields, table, properties, symbols, values, orderBy, pager);
	}*/
	
	
	
	/*public static List<Record> findByMultProperties(String dbSource, String table, String[] properties, String[] symbols, Object[] values) {
		return findByMultProperties(table, properties, symbols, values, null, null);
	}*/
	
	/*public static List<Record> findByMultProperties(String dbSource, String table, String[] properties, String[] symbols, Object[] values, Pager pager) {
		return findByMultProperties(table, properties, symbols, values, null, pager);
	}*/
	
	/*public static List<Record> findByMultProperties(String table, String[] properties, String[] symbols, Object[] values, String orderBy, Pager pager) {
		return findByMultProperties(table, properties, symbols, values, orderBy, pager);
	}*/
	/*
	 * sqlserver 2012版本以后分页
	 */
	public static List<Record> findByMultProperties(String[] fields, String table, String[] properties, String[] symbols, Object[] values, String orderBy, Pager pager) {
		StringBuilder sb = new StringBuilder("select ");
		if(fields == null || fields.length == 0) {
			fields = new String[]{"*"};
		}
		for (int i = 0; i < fields.length; i++) {
			if(i > 0) {
				sb.append(", ");
			}
			sb.append(fields[i]);
		}
		sb.append(" from " + table + " where 1=1");
		for (int i = 0; i < properties.length; i++) {
			sb.append(" and " + properties[i] + " " + symbols[i] + " ?");
		}
		if(StringUtil.isNotEmpty(orderBy)) {
			sb.append(" order by " + orderBy);
		}
		if(pager != null) {
			//sb.append(" limit " + pager.getStartRow() + ", " + pager.getRows());
			sb.append(" offset " + pager.getStartRow() + " rows fetch next " + pager.getRows()+" rows only");			
		}			
		return Db.find(sb.toString(), values);
	}
	
	/**
	 * sqlServer2012前低版本分页方式
	 * select * from(select row_number() over(order by id) as num,* from wms_instorage) as t where t.num between 0 and 20
	  
	public static List<Record> findByMultPropertiesDbSource(String dbSource, String[] fields, String table, String[] properties, String[] symbols, Object[] values, String orderBy, Pager pager) {
		StringBuilder sb = new StringBuilder("select ");
		if(fields == null || fields.length == 0) {
			fields = new String[]{"*"};
		}
		for (int i = 0; i < fields.length; i++) {
			if(i > 0) {
				sb.append(", ");
			}
			sb.append("t."+fields[i]);
		}
		sb.append(" from(select row_number() over(order by id) as num,* from " + table + ")as t where 1=1");
		for (int i = 0; i < properties.length; i++) {
			sb.append(" and t." + properties[i] + " " + symbols[i] + " ?");
		}
		if(pager != null) {
			sb.append(" and t.num between "+pager.getStartRow()+" and  " + (pager.getStartRow()+pager.getRows()));
						
		}
		if(StringUtil.isNotEmpty(orderBy)) {
			sb.append(" order by t." + orderBy);
		}
		
		return Db.use(ZcurdTool.getDbSource(dbSource)).find(sb.toString(), values);
	}
	*/
	public static int countByMultProperties(String table, String[] properties, Object[] values) {
		String[] symbols = new String[properties.length];
		for (int i = 0; i < properties.length; i++) {
			symbols[i] = "=";
		}
		return countByMultProperties(table, properties, symbols, values);
	}
	public static int countByMultProperties(String table, String[] properties, String[] symbols, Object[] values) {
		StringBuilder sb = new StringBuilder("select count(*)");
		sb.append(" from " + table + " where 1=1");
		for (int i = 0; i < properties.length; i++) {
			sb.append(" and " + properties[i] + " " + symbols[i] + " ?");
		}
		return Db.queryInt(sb.toString(), values);
	}
	
	public static List<Object> find(String selectSQL, String[] properties, String[] symbols, Object[] values) {
		StringBuilder sb = new StringBuilder(selectSQL);
		if(selectSQL.toLowerCase().indexOf("where") < 0) {
			sb.append(" where");
		}
		sb.append(" 1=1");
		for (int i = 0; i < properties.length; i++) {
			sb.append(" and " + properties[i] + " " + symbols[i] + " ?");
		}
		
		List<Object> result = Db.query(sb.toString(), values);
		return result;
	}
	
	/*public static DbPro use(String dbSource) {
		return Db.use(ZcurdTool.getDbSource(dbSource));
	}
		*/

}
